/*
 *  WsvJdbc - a JDBC driver for WSV (values separated by whatever) data files
 *  Copyright (C) 2004  Daniele Pes
 * 
 *  CsvJdbc - a JDBC driver for CSV files
 *  Copyright (C) 2001  Jonathan Ackerman
 *
 *  This library is free software; you can redistribute it and/or
 *  modify it under the terms of the GNU Lesser General Public
 *  License as published by the Free Software Foundation; either
 *  version 2.1 of the License, or (at your option) any later version.
 *  This library is distributed in the hope that it will be useful,
 *  but WITHOUT ANY WARRANTY; without even the implied warranty of
 *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
 *  Lesser General Public License for more details.
 *  You should have received a copy of the GNU Lesser General Public
 *  License along with this library; if not, write to the Free Software
 *  Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
 */
 
package axs.jdbc.driver;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.Reader;
import java.io.StringReader;
import java.io.UnsupportedEncodingException;
import java.net.URL;
import java.sql.SQLException;
import java.util.Vector;

import axs.jdbc.dataSourceConfiguration.JdbcSourceConfiguration;
import axs.jdbc.utils.ArrayUtilities;
import axs.jdbc.utils.ObjectUtilities;
import axs.jdbc.utils.SpitDataException;


/**
 * This class is a helper class that handles the reading and parsing of data
 * from a plain text data file.
 *
 * @author     Jonathan Ackerman
 * @author     Sander Brienen
 * @author     Stuart Mottram (fritto)
 * @author     Jason Bedell
 * @author     Tomasz Skutnik
 * @author 	   Daniele Pes
 * @author     Marco Pozzato
 * @author     Stefano Morello
 * @created    25 November 2001
 * @version    $Id: WsvReader.java,v 1.2 2007/04/16 17:24:36 montag451 Exp $
 */
public class WsvReader {
	private String[]  dateFormats;
	private BufferedReader input;
	private String[]  columnNames;
	private String[]  columns;
	private java.lang.String buf = null;
	private String 	separator = ",";
	private boolean 	suppressHeaders = false;
	private String 	tableName;
	private URL 	   fileURL;
	private String    charset = null;
	private Class[]   columnTypes;
	private Integer[] strColumnWidths;
	private Integer[] columnBeginBoundaries;
	private Integer[] columnEndBoundaries;
	private boolean   countingRecords;
	private int     	skipLines = 0;

	/**
	 * (1) Sets input buffered reader.
	 * (2) Sets Reader's column names.
	 * 
	 * @param fileURL
	 * @param separator
	 * @param suppressHeaders
	 * @param charset
	 * @param columnNames
	 * @param columnTypes
	 * @param countingRecords
	 * @throws java.lang.Exception
	 */
	public WsvReader(URL       fileURL, 
						  String    separator, 
						  boolean   suppressHeaders,
						  String    charset,
						  String[]  columnNames,
						  Class[]   columnTypes,
						  Integer[] strColumnWidths,
						  Integer[] columnBeginBoundaries,
						  Integer[] columnEndBoundaries,
						  boolean   countingRecords,
						  int       skipLines,
						  String[]  dateFormats)
	throws java.lang.Exception {
		setSeparator(separator);
		this.suppressHeaders = suppressHeaders;
		this.fileURL 		   = fileURL;
		this.charset 		   = charset;
		this.strColumnWidths = strColumnWidths;
		this.columnBeginBoundaries = columnBeginBoundaries;
		this.columnEndBoundaries = columnEndBoundaries;
		this.countingRecords = countingRecords;
		this.skipLines       = skipLines;
		this.dateFormats	   = dateFormats;		
		Reader inStr;
		inStr = null;
		if (countingRecords) {
			String stream;
			// (1)
			stream = getCountStream(charset);
			inStr = new StringReader(stream);
			input = new BufferedReader(inStr);
			int c = Integer.valueOf(stream).intValue(); 
			if(c > 0)
				skipFirstRows();
			// (2)
			this.columnNames = new String[] { "COUNT(*)" };
			// Note: the columns are set inside 'next', gathering a line of data from the source text file through the 'input' buffered reader.
			//       Both, columnNames and input were set according to the record count functionality -> 'next' will work properly. It's like
			//			a new source data file was built, as it was e.g.:
			//			COUNT(*)
			//			13
			// 		that is a table having, as first line, a list of column names composed by just the fake column named COUNT(*), and as second
			//			line the only line of data, holding the number of rows of the originale source data file.
			this.columnBeginBoundaries = new Integer[] { new Integer(1)};
			this.columnEndBoundaries = new Integer[] { new Integer(stream.length())};
			// note: it's incumbent on the parser to set SqlEveryRecordFakeType as type for 
			// this fake column (and WsvMetaData will recognize it as a NULL type)
		}
		else {
			// (1)
			setInputBufferedReader(fileURL);
			skipFirstRows();
			// (2)						
			setColumnNames(columnNames);
			setColumnTypes(columnTypes);
		}
	}		


	private void skipFirstRows() 
	throws SpitDataException{
		if(skipLines>0){
			for (int i = 0; i < skipLines; i++) {
				try {
					buf = input.readLine();
				}
				catch (IOException e) {
					throw new SpitDataException("Unable to skip the first " + skipLines + " of data file \n-> " + e.getMessage());
				}
			}
		}
	}
	

	private void setColumnNames(String[] columnNames) 
	throws UnsupportedEncodingException, 
			 IOException, 
			 SQLException {
		if (columnNames == null) {
			if (this.suppressHeaders) { // the first line contains data
				String[] data;
				// No column names are available. Read the first data line and determine the number of colums.
				buf = input.readLine();
				try {
					data = parseCsvLine(buf);
				}
				catch (SQLException e) {
					throw new SQLException("Unable to set column names \n -> " + e.getMessage());
				}
				this.columnNames = new String[data.length];
				for (int i = 0; i < data.length; i++) {
					// first position has index 0
					this.columnNames[i] = "COLUMN" + String.valueOf(i + 1);
				}
				data = null; // throw away.         
			}
			else {				
				next(); // columns is filled up with column names
				this.columnNames = columns; 
			}
		}
		else {
			if (!this.suppressHeaders) { // the first column contains column names
				next(); // this reads first line and fills up columns with column names
				try {
					if (!ObjectUtilities.compareObjectsForEquality(columns, columnNames)) {
						throw new SQLException(
							"Column names got from data file are different from those specified!"
								+ "\nColumn names as gathered froma data file: \n" + ArrayUtilities.printArray(columns)
								+ "\nColumn names as specified: \n" + ArrayUtilities.printArray(columnNames));
					}
				}
				catch (SpitDataException e) {
					throw new SQLException(
						"Column names got from data file are different from those specified \n-> " + e.getMessage());
				}
			}
			this.columnNames = columnNames;
		}
	}
	
	
	/**
	 * @param columnTypes
	 */
	private void setColumnTypes(Class[] columnTypes) {
		if(columnTypes != null)
			this.columnTypes = columnTypes;
		else{
			// at this point columnNames was defined
			this.columnTypes = new Class[columnNames.length];
			for (int i = 0; i < columnNames.length; i++){
				this.columnTypes[i] = String.class; // default is VARCHAR for all columns								
			}
		}		
	}
	
	
	private void setInputBufferedReader(URL fileURL) 
	throws UnsupportedEncodingException, 
			 IOException {
		if (charset != null) {
			input = new BufferedReader(new InputStreamReader(fileURL.openStream(), charset));
		}
		else {
			input = new BufferedReader(new InputStreamReader(fileURL.openStream()));
		}
	}
	

	private String getCountStream(String charset) 
	throws UnsupportedEncodingException, 
			 IOException, 
			 SQLException {
		String result = "";
		//
		if (charset != null)
			input = new BufferedReader(new InputStreamReader(fileURL.openStream(), charset));
		else
			input = new BufferedReader(new InputStreamReader(fileURL.openStream()));
		//
		int count;
		count = 0;
		boolean next = true;
		if (!suppressHeaders) { // the first line doesn't contain data			
			next = nextWithNoParsing(); // note: next = false and the stream is closed, if the source file is empty			
		}
		if (next) {
			while (nextWithNoParsing()) {
				count++;
			}
		}
		result = String.valueOf(count);
		return result;
	}
	
	
	/**
	 * Gets the columnNames attribute of the CsvReader object
	 *
	 * @return    The columnNames value
	 * @since
	 */
	public String[] getColumnNames() {
		return columnNames;
	}
	
	
	public String getTableName() {
		if (tableName != null)
			return tableName;
		String fileName = fileURL.getFile();
		int index = fileName.lastIndexOf(".");
		if (index > 0) {
			tableName = fileName.substring(0, index);
		}
		return tableName;
	}
	
	
	/**
	 * Gets the columnTypes attribute of the CsvReader object
	 *
	 * @return    The columnNames value
	 * @since
	 */
	public Class[] getColumnTypes() {
		return columnTypes;
	}
	
	
	/**
	 * Get the value of the column at the specified index.
	 *
	 * @param  columnIndex  Description of Parameter
	 * @return              The column value
	 * @since
	 */
	public String getColumn(int columnIndex) 
	throws SQLException {
		if (columnIndex >= columns.length) {
			return null;
		}
		return columns[columnIndex];
	}
	
	
	/**
	 * Get value from column at specified name.
	 * If the column name is not found, throw an error.
	 *
	 * @param  columnName     Description of Parameter
	 * @return                The column value
	 * @exception  SQLException  Description of Exception
	 * @since
	 */
	public String getColumn(String columnName) 
	throws SQLException {
		for (int loop = 0; loop < columnNames.length; loop++) {
			if (columnName.equalsIgnoreCase(columnNames[loop])
				|| columnName.equalsIgnoreCase(getTableName() + "." + columnNames[loop])) {
				return getColumn(loop);
			}
		}
		throw new SQLException("Column '" + columnName + "' not found.");
	}
	
	
	/**
	  * Get value Index of column at specified name.
	  * If the column name is not found, throw an error.
	  *
	  * @param  columnName     Description of Parameter
	  * @return                The column index
	  * @exception  SQLException  Description of Exception
	  * @since
	  */
	//PES
	public int getColumnIndex(String columnName) 
	throws SQLException {
		for (int loop = 0; loop < columnNames.length; loop++) {
			if (columnName.equalsIgnoreCase(columnNames[loop])
				|| columnName.equalsIgnoreCase(getTableName() + "." + columnNames[loop])) {
				return loop + 1;
			}
		}
		throw new SQLException("Column '" + columnName + "' not found.");
	}
	
	
	/**
	 *Description of the Method
	 *
	 * @return                Description of the Returned Value
	 * @exception  SQLException  Description of Exception
	 * @since
	 */
	public boolean next() 
	throws SQLException {		
		String dataLine = null;
		try { // (1)
			if (suppressHeaders && (buf != null)) {				
				// The buffer is not empty yet, so use this first.
				dataLine = buf;
				buf      = null;
			}
			else {
				// read new line of data from input.
				dataLine = input.readLine();
			}
			if (dataLine == null) {
				input.close();
				return false;
			}
		}
		catch (IOException e) {
			throw new SQLException(e.toString());
		}		
		try { // (2)
			if(columnNames != null)
				columns = new String[columnNames.length];
			columns = parseCsvLine(dataLine);
		}
		catch (SQLException e) {
			throw new SQLException("Unable to get next record\n -> " + e.getMessage());
		}
		return true;
	}
	
	
	// PES
	public boolean nextWithNoParsing() // fast next
	throws SQLException {
		String dataLine;
		//		
		dataLine = null;
		try {
			if (suppressHeaders && (buf != null)) {
				// The buffer is not empty yet, so use this first.
				dataLine = buf;
				buf      = null;
			}
			else {
				// read new line of data from input.
				dataLine = input.readLine();
			}
			if (dataLine == null) {
				input.close();
				return false;
			}
		}
		catch (IOException e) {
			throw new SQLException(e.toString());
		}		
		return true;
	}
	
	
	/**
	 *Description of the Method
	 *
	 * @since
	 */
	public void close() {
		try {
			input.close();
			buf = null;
		}
		catch (Exception e) {
		}
	}
	
	
	/**
	 * This fills up a 'values' vector using String values gathered from source data line
	 * 
	 * @param line
	 * @return
	 * @throws SQLException
	 */	
	protected String[] parseCsvLine(String line) throws SQLException {
		if(line == null)
			return null;
		if(line.length()==0){
			return null;
		}	
		return parseNonEmptyLine(line);
	}
	
	
	//	This code updated with code by Stuart Mottram to handle line breaks in fields
	// see bug #492063
	private String[] parseNonEmptyLine(String line)
	throws SQLException{
		Vector    values;
		boolean   inQuotedString, foundSeparator;
		String    value, orgLine;
		int       fullLine, howManyAddedValues;
		int       currentPos; // note: the source data string can be longer than (2^31)-1 //TODO: verify
		Character currentChar, nextChar, quote, sep;									
		//
		quote					 = new Character('"');
		values             = new Vector();
		sep    				 = getSeparator().trim().equals(JdbcSourceConfiguration.FIXED_LENGHT_SEPARATOR) ? null : new Character(getSeparator().charAt(0));
		inQuotedString     = false;
		value 		       = "";
		orgLine 		       = line;		
		fullLine 		    = 0;
		howManyAddedValues = 0;
		String[] retVal    = null;
		currentChar        = null;
		foundSeparator		 = false;
		try {
			while (fullLine == 0) {
				currentPos = 0;
				while (currentPos <= line.length()) {
					if (currentPos < line.length()){
						currentChar = new Character(line.charAt(currentPos));
						if (value.length() == 0 && currentChar.equals(quote) && !inQuotedString) { // skip FIRST quote (current item is a string)						
							currentPos++;
							inQuotedString = true; // we are inside of a string
							continue;
						}
						if (currentChar.equals(quote)) { // found a double quote						
							nextChar = new Character(line.charAt(currentPos + 1));
							if (nextChar.equals(quote)) {
								value += currentChar;
								currentPos++;
							}
							else {
								if (!inQuotedString) {
									throw new SQLException("Unexpected '\"' in position " + currentPos + ". Line=" + orgLine);
								}
								if(foundSeparatorInLastLoop(foundSeparator))															
									foundSeparator = false;						
								else
									foundSeparator = reachedSeparator(nextChar, sep, currentPos, howManyAddedValues);
								if (inQuotedString && !foundSeparator) {
									throw new SQLException(
										"Expecting " + getSeparator() + " in position " + (currentPos + 1) + ". Line=" + orgLine);
								}
								values.add(value);
								value = "";
								howManyAddedValues++;
								currentPos     = getPos(howManyAddedValues, currentPos);
								inQuotedString = false;
								currentPos++;
							}
						}
						else { // begin of CORE
							if(foundSeparatorInLastLoop(foundSeparator))
								foundSeparator = false;
						//	else
							foundSeparator = reachedSeparator(currentChar, sep, currentPos, howManyAddedValues);
							if (foundSeparator) {
								if (inQuotedString) {
									value += currentChar;
								}
								else {
									if(!value.equals(sep))
                                        values.add(value);
                                    else
                                        values.add("");
                                    value = "";
                                    howManyAddedValues++;
								}
								currentPos = getPos(howManyAddedValues, currentPos);
							}
							else {
								value += currentChar;
							}
						}		 // end of CORE
					}
					else{
						values.add(value);
						value = "";
						howManyAddedValues++;
					}
					currentPos++;
				}
				if (inQuotedString) { // Remove extra , added at start					
					value = value.substring(0, value.length() - 1);
					try {
						line = input.readLine();
					}
					catch (IOException e) {
						throw new SQLException(e.toString());
					}
				}
				else {
					fullLine = 1;
				}
			}
			retVal = new String[values.size()];
			values.copyInto(retVal);
		}
		catch (Exception e) {
			String reason;
			//
			reason = "Unable to parse data line (" + line + "): " + e.getMessage();
			if (e instanceof StringIndexOutOfBoundsException)
				reason += "\n-> possible reason: first line includes column names but you specified differently!";
			throw new SQLException(reason);
		}
		return retVal;
	}
	
	
	private int getPos(int columnIndex, 
							 int currentPos) {
		int result;
		//
		if(getSeparator().trim().equals(JdbcSourceConfiguration.FIXED_LENGHT_SEPARATOR)){
			result = columnIndex < columnBeginBoundaries.length ? 
						columnBeginBoundaries[columnIndex].intValue()-2 : 
						currentPos; 
		}
		else{
			result = currentPos;
		}			
		return result;
	}
	
	
	/**
	 * @param foundSeparator
	 * @return
	 */
	private boolean foundSeparatorInLastLoop(boolean foundSeparator) {
		return foundSeparator;
	}
	
	
	/**
	 * For a 'conventional' separator, that is not a fixed lenght pseudo-separator, its position is
	 * subsequent to that of the last character of the data present in that column (starting to count from 0). 
	 * In case it's a fixed lenght pseudo-separator, then it is reached if curPos (always pointing to the char 
	 * preceding curChar when reachedSeparator is called) is so that curPos+1 = upperBound of current column range.
	 * Note: not necessarily all the columns of a fixed lenght data file have to be involved. There
	 * can be ordinal discontinuity in the sequence of begginning and ending boundaries, e.g.:
	 * 1  2  3  4  5  6  7  8  9  10  11  12  13
	 * a  b  c  d  e  f  j  k  l  m   n   o   p 
	 * and the sequence of boundaries could be specified as. 1-3, 7-9, 10-13
	 * in this case, when the upper ending boundary is reached, it's necessary to set curPos to the
	 * next beginning boundary index. So, if curChar == 'c' and curPos == 2 it must be set curPos=6 
	 * (== next beginning pos (7) decreased by 1 => it will be increased in the parser).
	 * 
	 * @pre-condition curPos refers to the character preceding curChar
	 * @param curChar
	 * @param separator
	 * @param columnIndex first column has 0
	 * @return
	 */
	private boolean reachedSeparator(Character curChar, 
												Character sep,
										      int       curPos, 
										      int       columnIndex) {
		boolean result;
		//
		result = false;
		if (getSeparator().trim().equals(JdbcSourceConfiguration.FIXED_LENGHT_SEPARATOR)) {
			// note: starting pos for curPos is 0 and (curPos+1)==position of curChar (starting from 0)
			//		 starting pos for column boundaries is 1 => columnEndBoundaries[columnIndex]-1 == ending
			//	    boundary (starting from 0) 			 
			if ((curPos) == (columnEndBoundaries[columnIndex].intValue())) {
				result = true;				
				// curPos will be incremented at the end of current loop. At the beginning of the next loop it will be
			}
		}
		else {			
			result = (curChar.equals(sep));
		}
		//
		return result;
	}
			
	public String[] getDateFormats() {
		return dateFormats;
	}
   
   public String getSeparator() {
      return separator;
   }
   
   private void setSeparator(String separator) {
      this.separator = separator;
   }
}
